package com.ruoyi.web.service;


import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.DayOfWeek;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.time.temporal.IsoFields;
import java.time.temporal.TemporalAdjusters;
import java.util.*;


@Service
public class C2DeploySucRateService {

    @Autowired
    private DruidDataSource druidDataSource;

    /**
     * 获取每个自然周内的 C2 发布成功率
     *
     * @return 包含时间段、总条目数、成功条目数和百分比的结果集
     */
    public Map<String, Object> getC2DeploySucRateByWeeks() {
        // 返回数据的结构
        Map<String, List<String>> timePeriods = new HashMap<>();
        List<String> weeks = new ArrayList<>();
        List<Integer> totalCounts = new ArrayList<>();
        List<Integer> successCounts = new ArrayList<>();
        List<Double> percentages = new ArrayList<>();

        // SQL 查询语句，查询所有的 otaTime 和 otaStatus
        String sql = "SELECT `otaTime`, COALESCE(`otaStatus`, 0) AS `otaStatus` FROM `ota_info`";

        // 存储每周的统计数据 (TreeMap 按周排序)
        Map<String, int[]> weeklyStats = new TreeMap<>();

        try (Connection conn = druidDataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {

            // 遍历查询结果集
            while (rs.next()) {
                LocalDateTime otaTime = rs.getObject("otaTime", LocalDateTime.class);
                int otaStatus = rs.getInt("otaStatus");

                // 获取自然周周期，例如 "2024-W45"
                String week = getWeekPeriod(otaTime);

                // 初始化统计数据
                weeklyStats.putIfAbsent(week, new int[]{0, 0});

                // 更新统计：总条目数和 otaStatus 为 0 的条目数
                weeklyStats.get(week)[0]++; // 总条目数加 1
                if (otaStatus == 0) { // 假设 otaStatus 为 0 表示成功
                    weeklyStats.get(week)[1]++; // 成功条目数加 1
                }
            }

            // 遍历统计数据，计算每周的成功率
            for (Map.Entry<String, int[]> entry : weeklyStats.entrySet()) {
                String week = entry.getKey();
                int[] stats = entry.getValue();
                int totalCount = stats[0];
                int successCount = stats[1];

                // 计算百分比，保留两位小数
                double percentage = totalCount > 0
                        ? new BigDecimal(successCount * 100.0 / totalCount)
                        .setScale(2, RoundingMode.HALF_UP)
                        .doubleValue()
                        : 0.0;

                // 填充返回值
                weeks.add(week);
                totalCounts.add(totalCount);
                successCounts.add(successCount);
                percentages.add(percentage);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        // 构造返回数据
        timePeriods.put("week", weeks);
        Map<String, Object> response = new HashMap<>();
        response.put("timePeriods", timePeriods);
        response.put("totalCounts", totalCounts);
        response.put("successCounts", successCounts);
        response.put("percentages", percentages);

        return response;
    }

    public Map<String, Object> getC2DeploySucRateByYears() {
        // 返回数据的结构
        Map<String, List<String>> timePeriods = new HashMap<>();
        List<String> years = new ArrayList<>();
        List<Integer> totalCounts = new ArrayList<>();
        List<Integer> successCounts = new ArrayList<>();
        List<Double> percentages = new ArrayList<>();

        // SQL 查询语句，查询所有的 otaTime 和 otaStatus
        String sql = "SELECT `otaTime`, COALESCE(`otaStatus`, 0) AS `otaStatus` FROM `ota_info`";

        // 存储每年的统计数据 (TreeMap 按年排序)
        Map<String, int[]> yearlyStats = new TreeMap<>();

        try (Connection conn = druidDataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {

            // 遍历查询结果集
            while (rs.next()) {
                LocalDateTime otaTime = rs.getObject("otaTime", LocalDateTime.class);
                int otaStatus = rs.getInt("otaStatus");

                // 获取自然年周期，例如 "2024"
                String year = String.valueOf(otaTime.getYear());

                // 初始化统计数据
                yearlyStats.putIfAbsent(year, new int[]{0, 0});

                // 更新统计：总条目数和 otaStatus 为 0 的条目数
                yearlyStats.get(year)[0]++; // 总条目数加 1
                if (otaStatus == 0) { // 假设 otaStatus 为 0 表示成功
                    yearlyStats.get(year)[1]++; // 成功条目数加 1
                }
            }

            // 遍历统计数据，计算每年的成功率
            for (Map.Entry<String, int[]> entry : yearlyStats.entrySet()) {
                String year = entry.getKey();
                int[] stats = entry.getValue();
                int totalCount = stats[0];
                int successCount = stats[1];

                // 计算百分比，保留两位小数
                double percentage = totalCount > 0
                        ? new BigDecimal(successCount * 100.0 / totalCount)
                        .setScale(2, RoundingMode.HALF_UP)
                        .doubleValue()
                        : 0.0;

                // 填充返回值
                years.add(year);
                totalCounts.add(totalCount);
                successCounts.add(successCount);
                percentages.add(percentage);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        // 构造返回数据
        timePeriods.put("year", years);
        Map<String, Object> response = new HashMap<>();
        response.put("timePeriods", timePeriods);
        response.put("totalCounts", totalCounts);
        response.put("successCounts", successCounts);
        response.put("percentages", percentages);

        return response;
    }

    /**
     * 根据 LocalDateTime 获取自然周周期，例如 "2024-W45"
     *
     * @param releaseTime 发布时间
     * @return 格式化后的自然周周期
     */
    private String getWeekPeriod(LocalDateTime releaseTime) {
        // 将 LocalDateTime 转换为 LocalDate，只保留日期部分
        LocalDate date = releaseTime.toLocalDate();

        // 获取年份和该日期的自然周
        int year = date.getYear();
        int week = releaseTime.get(IsoFields.WEEK_OF_WEEK_BASED_YEAR);

        // 获取该周的开始日期（周一）
        LocalDate startOfWeek = date.with(IsoFields.WEEK_OF_WEEK_BASED_YEAR, week)
                .with(IsoFields.WEEK_BASED_YEAR, year)
                .with(TemporalAdjusters.previousOrSame(DayOfWeek.MONDAY));

        // 获取该周的结束日期（周日）
        LocalDate endOfWeek = startOfWeek.plusDays(6);

        // 使用 "MM/dd" 格式化日期
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("MM/dd");
        return startOfWeek.format(formatter) + "-" + endOfWeek.format(formatter);
    }


    public Map<String, Object> getC2DeploySucRateByMonth() {
        // 返回数据的结构
        Map<String, List<String>> timePeriods = new HashMap<>();
        List<String> months = new ArrayList<>();
        List<Integer> totalCounts = new ArrayList<>();
        List<Integer> successCounts = new ArrayList<>();
        List<Double> percentages = new ArrayList<>();

        // SQL 查询语句，查询所有的 buildTime 和 pubStatus
        String sql = "SELECT `otaTime`, COALESCE(`otaStatus`, 0) AS `otaStatus` FROM `ota_info`;";

        // 存储每个月的统计数据 (TreeMap 按月排序)
        Map<String, int[]> monthlyStats = new TreeMap<>();

        try (Connection conn = druidDataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {

            // 遍历查询结果集
            while (rs.next()) {
                LocalDateTime buildTime = rs.getObject("otaTime", LocalDateTime.class);
                int otaStatus = rs.getInt("otaStatus");

                // 获取自然月周期，例如 "2024-01"
                String month = getMonthPeriod(buildTime);

                // 初始化统计数据
                monthlyStats.putIfAbsent(month, new int[]{0, 0});

                // 更新统计：总条目数和 otaStatus 为 0 的条目数
                monthlyStats.get(month)[0]++; // 总条目数加 1
                if (otaStatus == 0) {
                    monthlyStats.get(month)[1]++; // 成功条目数加 1
                }
            }

            // 遍历统计数据，计算每月的成功率
            for (Map.Entry<String, int[]> entry : monthlyStats.entrySet()) {
                String month = entry.getKey();
                int[] stats = entry.getValue();
                int totalCount = stats[0];
                int successCount = stats[1];

                // 计算百分比，保留两位小数
                double percentage = totalCount > 0
                        ? new BigDecimal(successCount * 100.0 / totalCount)
                        .setScale(2, RoundingMode.HALF_UP)
                        .doubleValue()
                        : 0.0;

                // 填充返回值
                months.add(month);
                totalCounts.add(totalCount);
                successCounts.add(successCount);
                percentages.add(percentage);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        // 构造返回数据
        timePeriods.put("month", months);
        Map<String, Object> response = new HashMap<>();
        response.put("timePeriods", timePeriods);
        response.put("totalCounts", totalCounts);
        response.put("successCounts", successCounts);
        response.put("percentages", percentages);

        return response;
    }


    public Map<String, Object> getC2DeploySucRateByQuarters() {
        // 返回数据的结构
        Map<String, List<String>> timePeriods = new HashMap<>();
        List<String> quarters = new ArrayList<>();
        List<Integer> totalCounts = new ArrayList<>();
        List<Integer> successCounts = new ArrayList<>();
        List<Double> percentages = new ArrayList<>();

        // SQL 查询语句，查询所有的 otaTime 和 otaStatus
        String sql = "SELECT `otaTime`, COALESCE(`otaStatus`, 0) AS `otaStatus` FROM `ota_info`";

        // 存储每个季度的统计数据 (TreeMap 按季度排序)
        Map<String, int[]> quarterlyStats = new TreeMap<>();

        try (Connection conn = druidDataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {

            // 遍历查询结果集
            while (rs.next()) {
                LocalDateTime otaTime = rs.getObject("otaTime", LocalDateTime.class);
                int otaStatus = rs.getInt("otaStatus");

                // 获取自然季度周期，例如 "2024-Q1"
                String quarter = getQuarterPeriod(otaTime);

                // 初始化统计数据
                quarterlyStats.putIfAbsent(quarter, new int[]{0, 0});

                // 更新统计：总条目数和 otaStatus 为 0 的条目数
                quarterlyStats.get(quarter)[0]++; // 总条目数加 1
                if (otaStatus == 0) { // 假设 otaStatus 为 0 表示成功
                    quarterlyStats.get(quarter)[1]++; // 成功条目数加 1
                }
            }

            // 遍历统计数据，计算每季度的成功率
            for (Map.Entry<String, int[]> entry : quarterlyStats.entrySet()) {
                String quarter = entry.getKey();
                int[] stats = entry.getValue();
                int totalCount = stats[0];
                int successCount = stats[1];

                // 计算百分比，保留两位小数
                double percentage = totalCount > 0
                        ? new BigDecimal(successCount * 100.0 / totalCount)
                        .setScale(2, RoundingMode.HALF_UP)
                        .doubleValue()
                        : 0.0;

                // 填充返回值
                quarters.add(quarter);
                totalCounts.add(totalCount);
                successCounts.add(successCount);
                percentages.add(percentage);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        // 构造返回数据
        timePeriods.put("quarter", quarters);
        Map<String, Object> response = new HashMap<>();
        response.put("timePeriods", timePeriods);
        response.put("totalCounts", totalCounts);
        response.put("successCounts", successCounts);
        response.put("percentages", percentages);

        return response;
    }



    /**
     * 根据 LocalDateTime 获取自然月周期，例如 "2024-01"
     */
    private String getMonthPeriod(LocalDateTime buildTime) {
        return buildTime.getYear() + "-" + String.format("%02d", buildTime.getMonthValue());
    }


    /**
     * 根据 LocalDateTime 获取自然季度周期，例如 "2024-Q1"
     */
    private String getQuarterPeriod(LocalDateTime buildTime) {
        int year = buildTime.getYear();
        int month = buildTime.getMonthValue();
        int quarter = (month - 1) / 3 + 1; // 计算季度，1月-3月为Q1，依此类推
        return year + "-第" + quarter + "季度";
    }
}



//@Service
//public class C2DeploySucRateService {
//
//    @Autowired
//    private DruidDataSource druidDataSource;
//
//    /**
//     * 获取每个自然月内的 C2 发布成功率
//     *
//     * @return 包含时间段、总条目数、成功条目数和百分比的结果集
//     */
//    public Map<String, Object> getC2DeploySucRateByMonths() {
//        // 返回数据的结构
//        Map<String, List<String>> timePeriods = new HashMap<>();
//        List<String> months = new ArrayList<>();
//        List<Integer> totalCounts = new ArrayList<>();
//        List<Integer> successCounts = new ArrayList<>();
//        List<Double> percentages = new ArrayList<>();
//
//        // SQL 查询语句，查询所有的 buildTime 和 pubStatus
//        String sql = "SELECT `otaTime`, COALESCE(`otaStatus`, 0) AS `otaStatus` FROM `ota_info`;";
//
//        // 存储每个月的统计数据 (TreeMap 按月排序)
//        Map<String, int[]> monthlyStats = new TreeMap<>();
//
//        try (Connection conn = druidDataSource.getConnection();
//             PreparedStatement stmt = conn.prepareStatement(sql);
//             ResultSet rs = stmt.executeQuery()) {
//
//            // 遍历查询结果集
//            while (rs.next()) {
//                LocalDateTime buildTime = rs.getObject("otaTime", LocalDateTime.class);
//                int otaStatus = rs.getInt("otaStatus");
//
//                // 获取自然月周期，例如 "2024-01"
//                String month = getMonthPeriod(buildTime);
//
//                // 初始化统计数据
//                monthlyStats.putIfAbsent(month, new int[]{0, 0});
//
//                // 更新统计：总条目数和 otaStatus 为 1 的条目数
//                monthlyStats.get(month)[0]++; // 总条目数加 1
//                if (otaStatus == 1) {
//                    monthlyStats.get(month)[1]++; // 成功条目数加 1
//                }
//            }
//
//            // 遍历统计数据，计算每月的成功率
//            for (Map.Entry<String, int[]> entry : monthlyStats.entrySet()) {
//                String month = entry.getKey();
//                int[] stats = entry.getValue();
//                int totalCount = stats[0];
//                int successCount = stats[1];
//
//                // 计算百分比，保留两位小数
//                double percentage = totalCount > 0
//                        ? new BigDecimal(successCount * 100.0 / totalCount)
//                        .setScale(2, RoundingMode.HALF_UP)
//                        .doubleValue()
//                        : 0.0;
//
//                // 填充返回值
//                months.add(month);
//                totalCounts.add(totalCount);
//                successCounts.add(successCount);
//                percentages.add(percentage);
//            }
//
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//
//        // 构造返回数据
//        timePeriods.put("month", months);
//        Map<String, Object> response = new HashMap<>();
//        response.put("timePeriods", timePeriods);
//        response.put("totalCounts", totalCounts);
//        response.put("successCounts", successCounts);
//        response.put("percentages", percentages);
//
//        return response;
//    }
//
//    /**
//     * 根据 LocalDateTime 获取自然月周期，例如 "2024-01"
//     */
//    private String getMonthPeriod(LocalDateTime buildTime) {
//        return buildTime.getYear() + "-" + String.format("%02d", buildTime.getMonthValue());
//    }
//}